﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DAL
{
    public class SqlHelper
    {
        //连接字符串
        static string strConn = ConfigurationManager.ConnectionStrings["StuManage"].ConnectionString;
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sp"></param>
        /// <returns></returns>
        public static DataTable QueryMethod(string sql, SqlParameter[] sp = null)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, strConn);
                DataTable dt = new DataTable();
                if (sp != null)
                {
                    sda.SelectCommand.Parameters.AddRange(sp);
                }
                sda.Fill(dt);
                return dt;
            }

        }
        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sp"></param>
        /// <returns></returns>
        public static int NonQueryMethod(string sql, SqlParameter[] sp = null)
        {
            int num = 0;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand comm = new SqlCommand(sql, conn);
                if (sp != null)//判断参数是否为空
                {
                    comm.Parameters.AddRange(sp);
                }
                try
                {
                    conn.Open();
                    num = comm.ExecuteNonQuery();
                }
                catch (Exception ex)
                {

                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
                return num;
            }
        }
        /// <summary>
        /// 查询的存储过程
        /// </summary>
        public static DataTable CunChu(string sql, SqlParameter[] sp = null, CommandType type = CommandType.Text)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand comm = new SqlCommand();

                if (sp != null)//判断参数是否为空
                {
                    //指定命令类型
                    //comm.CommandType = CommandType.StoredProcedure;
                    comm.CommandType = type;
                    comm.Parameters.AddRange(sp);
                    comm.CommandText = sql;//指定cmd的执行文本（可以是执行命令，表名或存储过程）
                    comm.Connection = conn;//指定连接对象
                   
                }
                try
                {
                    conn.Open();

                    SqlDataAdapter sda = new SqlDataAdapter(comm);

                    DataTable dt = new DataTable();
                    sda.Fill(dt);


                    return dt;
                }
                catch (Exception ex)
                {

                    throw ex;
                }
                finally
                {
                    conn.Close();
                }

            }

        }
        /// <summary>
        /// 执行增删改的存储过程的方法
        /// </summary>
        /// <param name="text">指定cmd执行的文本</param>
        /// <param name="sp">参数</param>
        /// <param name="type">指定cmd执行的文本的类型</param>
        /// <returns>返回受影响行数</returns>
        public static int NoQueryMetodProc(string text, SqlParameter[] sp = null, CommandType type = CommandType.Text)
        {
            int num = 0;

            using (SqlConnection conn=new SqlConnection(strConn))
            {
                //添加可以执行存储过程的对象
                SqlCommand cmd = new SqlCommand();

                cmd.Connection = conn;  // 指定cmd使用的连接对象
                cmd.CommandText = text; // 指定cmd执行的文本
                cmd.CommandType = type; // 指定cmd执行的文本的类型
                if (sp !=null)
                {
                    //将参数添加给执行对象
                    cmd.Parameters.AddRange(sp); 
                }
                try
                {
                    conn.Open();
                    //执行存储过程并返回受影响行数
                    
                    num = cmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally 
                { conn.Close(); }
            }
            return num;
        }
    }
}
